﻿Option Explicit On
'*******************************************************************
' Program: pkgVisio
' Author: Albert E Edlund
' Date:
'
' Purpose:
' Comments/Base: 
' http://support.microsoft.com/kb/306022
'
'*******************************************************************

#Region " Imports "

Imports ADODB
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Imports System.Reflection
Imports System.Runtime.InteropServices

Imports Excel = Microsoft.Office.Interop.Excel

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging

#End Region


Module modExcel


#Region " properties and declarations "



    Private _Excel As Excel.Application
    Public Property xlsApp As Excel.Application
        Get
            xlsApp = _Excel
        End Get
        Set(value As Excel.Application)
            _Excel = value
        End Set
    End Property

    Private _Workbooks As Excel.Workbooks
    Public Property xlsWorkbooks As Excel.Workbooks
        Get
            xlsWorkbooks = _Workbooks
        End Get
        Set(value As Excel.Workbooks)
            _Workbooks = value
        End Set
    End Property

    Private _Workbook As Excel.Workbook
    Public Property xlsWorkbook As Excel.Workbook
        Get
            xlsWorkbook = _Workbook
        End Get
        Set(value As Excel.Workbook)
            _Workbook = value
        End Set
    End Property

    Private _WorkSheets As Excel.Worksheets
    Public Property xlsWorkSheets As Excel.Worksheets
        Get
            xlsWorkSheets = _WorkSheets
        End Get
        Set(value As Excel.Worksheets)
            _WorkSheets = value
        End Set
    End Property

    Private _WorkSheet As Excel.Worksheet
    Public Property xlsWorkSheet As Excel.Worksheet
        Get
            xlsWorkSheet = _WorkSheet
        End Get
        Set(value As Excel.Worksheet)
            _WorkSheet = value
        End Set
    End Property


#End Region

    '
    ' Reads a workbook and returns a collection of worksheet names as a 
    ' dictionary
    '
    Public Function readExcelWorksheetList _
        (ByVal strWorkbook As String) _
        As Dictionary(Of Int16, String)

        Dim dictReturn As Dictionary(Of Int16, String) = New Dictionary(Of Int16, String)

        Try

            If (strWorkbook Is Nothing) Then
                Throw New System.ArgumentNullException("strWorkbook", _
                    "Workbook is Null.")
                GoTo ExitFunction
            End If

            If File.Exists(strWorkbook) = False Then
                MsgBox("Workbook (" & strWorkbook & ") not found")
                GoTo ExitFunction
            End If

            Dim ExcelSheetName As String = ""
            If xlsApp Is Nothing Then
                xlsApp = CreateObject("Excel.Application")
            End If

            xlsWorkbook = xlsApp.Workbooks.Open(strWorkbook)
            Dim intX As Integer = 1
            For Each objWorkSheet In xlsWorkbook.Worksheets
                dictReturn.Add(intX, objWorkSheet.Name)
                intX = intX + 1
            Next

            xlsApp.Quit()
            xlsApp = Nothing
            GC.Collect()

ExitFunction:

        Catch com As COMException
            ' add com exception handler here
            '********* visio snippet vb.net vis0056vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return dictReturn

    End Function


    '
    ' read a worksheet and return a datatable
    '
    Public Function readExcelWorksheet _
            (ByVal strWorkBook As String, _
            ByVal strWorkSheet As String) _
            As System.Data.DataTable


        Dim dtReturn As System.Data.DataTable = Nothing

        Try

            If (strWorkBook Is Nothing) _
                Or strWorkBook = "" Then
                Throw New System.ArgumentNullException("strWorkbook", _
                    "Workbook is Null.")
                GoTo ExitFunction
            End If

            If File.Exists(strWorkBook) = False Then
                MsgBox("Workbook (" & strWorkBook & ") not found")
                GoTo ExitFunction
            End If

            Dim DS As DataSet
            Dim MyCommand As OleDb.OleDbDataAdapter
            Dim MyConnection As OleDb.OleDbConnection
            Dim strConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                          & "Data Source=" & strWorkBook & ";" _
                                          & "Extended Properties=" & """Excel 12.0 Xml;HDR=YES""" & ";"
            MyConnection = New OleDb.OleDbConnection(strConnection)
            MyCommand = New OleDb.OleDbDataAdapter( _
                         "select * from [" & strWorkSheet & "$]", MyConnection)
            DS = New System.Data.DataSet()
            MyCommand.Fill(DS)
            dtReturn = DS.Tables(0)
            dtReturn.TableName = strWorkSheet
            MyConnection.Close()

ExitFunction:

        Catch oledb As OleDbException
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(oledb, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch com As COMException
            ' add com exception handler here
            '********* visio snippet vb.net vis0056vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return dtReturn

    End Function

    '
    ' pass in a dictionary of cells and column names, a starting cell for the range, 
    ' and a two dimensional array
    '
    Public Sub writeExcelWithArray _
        (ByVal DataArray As System.Array, _
         ByVal strPathFileName As String, _
         Optional ByVal dictColumns As Dictionary(Of String, String) = Nothing, _
         Optional ByVal cellStart As String = "A2")

        Try

            If strPathFileName Is Nothing _
                Or strPathFileName = "" Then Exit Sub

            ' how many dimensions
            Dim intDimensions As Integer = DataArray.Rank
            If intDimensions <> 2 Then Exit Sub

            Dim intRows As Integer = DataArray.GetLength(0)
            Dim intCols As Integer = DataArray.GetLength(1)

            'Start a new workbook in Excel.
            xlsApp = CreateObject("Excel.Application")
            xlsWorkbook = xlsApp.Workbooks.Add
            xlsWorkSheet = xlsWorkbook.Worksheets(1)

            'Add headers to the worksheet on row 1.
            If dictColumns IsNot Nothing Then
                Dim intX As Integer = 0
                For intX = 0 To dictColumns.Count - 1
                    xlsWorkSheet.Range(dictColumns.ElementAt(intX).Key.ToString).Value = dictColumns.ElementAt(intX).Value.ToString
                Next intX
            End If

            'Transfer the array to the worksheet starting at cell start.
            xlsWorkSheet.Range(cellStart).Resize(intRows, intCols).Value = DataArray

            'Save the Workbook and quit Excel.
            xlsWorkbook.SaveAs(strPathFileName)
            xlsWorkSheet = Nothing
            xlsWorkbook = Nothing
            xlsApp.Quit()
            xlsApp = Nothing
            GC.Collect()

            MsgBox("export of array to excel complete")

        Catch com As COMException
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



End Module
